package bbs2.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import bbs2.model.UserGroup;

public class UserGroupDAO {
    
    private static UserGroup makeUserGroup(ResultSet rs, boolean count) throws Exception {
        UserGroup g = new UserGroup();
        g.setId(rs.getInt("ID"));
        g.setName(rs.getString("name"));
        g.setCategory(rs.getString("category"));
        g.setCount(count ? rs.getInt("count") : 0);
        return g;
    }

    public static UserGroup selectByID(int id) throws Exception {
        UserGroup group = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "SELECT * FROM [UserGroup] WHERE [id] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()) 
                group = makeUserGroup(resultSet, false);
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return group;
    }
    
    public static UserGroup selectByName(String name) throws Exception {
        UserGroup group = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "SELECT * FROM [UserGroup] WHERE [name] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            if (resultSet.next()) 
                group = makeUserGroup(resultSet, false);
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return group;
    }

    public static ArrayList<Integer> selectByUserID(int userID) throws Exception {
        ArrayList<Integer> a = new ArrayList<Integer>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "SELECT [userGroupID] FROM [User_UserGroup] WHERE [userID] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) 
                a.add(resultSet.getInt("userGroupID"));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return a;
    }

    public static ArrayList<UserGroup> selectAll() throws Exception {
        ArrayList<UserGroup> a = new ArrayList<UserGroup>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "SELECT * FROM [UserGroup] WHERE [ID] > 0 ORDER BY [category], [name]";             
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) 
                a.add(makeUserGroup(resultSet, false));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return a;
    }

    public static ArrayList<UserGroup> selectAllWithCount(int order) throws Exception {
        ArrayList<UserGroup> a = new ArrayList<UserGroup>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "EXEC userGroupSelectAllWithCount ?";             
            statement = connection.prepareStatement(sql);
            statement.setInt(1, order);
            resultSet = statement.executeQuery();
            while (resultSet.next()) 
                a.add(makeUserGroup(resultSet, true));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return a;
    }
    
    public static void saveUserGroup(int userID, int[] userGroupIDList) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            connection.setAutoCommit(false);            
            String sql = "DELETE FROM [User_UserGroup] WHERE [userID] = ?";  
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userID);
            statement.executeUpdate();
            statement.close();
            
            sql = "INSERT [User_UserGroup] ([userID], [userGroupID]) VALUES (?, ?)"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userID);
            for (int userGroopID : userGroupIDList) {
                statement.setInt(2, userGroopID);
                statement.executeUpdate();
            }
            connection.commit();
        } catch(Exception e) {
            connection.rollback();
            throw e;        
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }  

    public static void update(int id, String name, String category) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "UPDATE [UserGroup] SET [name] = ?, [category] = ? WHERE [id] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setString(2, category);
            statement.setInt(3, id);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }

    public static void removeUser(int id, int[] userIDList) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "DELETE FROM [User_UserGroup] WHERE [userGroupID] = ? AND [userID] = ?";  
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            for (int userID : userIDList) {
                statement.setInt(2, userID);
                statement.executeUpdate();
            }
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }
    
    public static void addUser(int id, String[] loginIDList) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "EXEC userGroupAddUser ?, ?";  
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            for (String loginID : loginIDList) {
                statement.setString(2, loginID);
                statement.executeUpdate();
            }
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }
    
    public static void deleteUserGroup(int id) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "EXEC userGroupDelete ?";  
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }
    
    public static void insert(String title, String category) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "INSERT INTO [UserGroup] ([name], [category]) VALUES (?,?)";  
            statement = connection.prepareStatement(sql);
            statement.setString(1, title);
            statement.setString(2, category);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }
}
